/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;

import java.text.*;
import java.util.Vector;

/**
 *
 * @author VIC
 */
public class NhatKyHT_Model {

    public NhatKyHT_Model() {
    }

    public static void GhiNhatKy(int maltt, int manv, String mats) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();

            Calendar cal = Calendar.getInstance();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            String ngaygio = fm.format(cal.getTime());
            String query = "insert into nhatkyhethong(ngaygio,maltt,manv,mats) values ('" + ngaygio + "'," + maltt + "," + manv + ",'" + mats + "')";
            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LayDSNhatKyHT(int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " "
                    + " Order by MaNK desc";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static String LayTenThaoTac(int maltt, int langid) {

        String tentt = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenLTT from loaithaotac where MaLTT = " + maltt + " and MaNN = " + langid;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tentt = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tentt;
    }

    public static String LayTenThamSo(String mats, int langid) {

        String tents = "";
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenTS from thamsohethong where MaTS = '" + mats + "' and MaNN = " + langid;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tents = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tents;
    }

    public Vector SearchNhatKy(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String nhanvien, String thaotac, String doituong, int langid) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from nhatkyhethong ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " ";

            String[] arr = {nhanvien, thaotac, doituong};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(doituong)) {
                    query = query + " and TenTS like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(thaotac)) {
                    query = query + " and TenLTT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(nhanvien)) {
                    query = query + " and HoTen like '%" + arr[i] + "%' ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by MaNK desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayGio(int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " "
                    + " Order by NgayGio " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNhanVien(int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " "
                    + " Order by HoTen " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortThaoTacNK(int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " "
                    + " Order by TenLTT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortDoiTuongNK(int langid, String order) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " "
                    + " Order by TenTS " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayGioNK(int langid, String order, String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String nhanvien, String thaotac, String doituong) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from nhatkyhethong ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " ";
            String[] arr = {nhanvien, thaotac, doituong};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(doituong)) {
                    query = query + " and TenTS like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(thaotac)) {
                    query = query + " and TenLTT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(nhanvien)) {
                    query = query + " and HoTen like '%" + arr[i] + "%' ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by NgayGio " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNhanVienNK(int langid, String order, String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String nhanvien, String thaotac, String doituong) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from nhatkyhethong ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " ";
            String[] arr = {nhanvien, thaotac, doituong};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(doituong)) {
                    query = query + " and TenTS like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(thaotac)) {
                    query = query + " and TenLTT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(nhanvien)) {
                    query = query + " and HoTen like '%" + arr[i] + "%' ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by HoTen " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchThaoTacNK(int langid, String order, String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String nhanvien, String thaotac, String doituong) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from nhatkyhethong ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " ";
            String[] arr = {nhanvien, thaotac, doituong};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(doituong)) {
                    query = query + " and TenTS like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(thaotac)) {
                    query = query + " and TenLTT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(nhanvien)) {
                    query = query + " and HoTen like '%" + arr[i] + "%' ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by TenLTT " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchDoiTuongNK(int langid, String order, String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut, String nhanvien, String thaotac, String doituong) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            String rsdate = "";
            int n = 0;
            if (tungay.equals("") && denngay.equals("")) {
                n = n + 1;
            } else {
                Calendar c1 = Calendar.getInstance();
                Calendar c2 = Calendar.getInstance();
                Calendar c3 = Calendar.getInstance();
                Date date1 = fm.parse(tungay);
                Date date2 = fm.parse(denngay);
                c1.setTime(date1);
                c2.setTime(date2);
                String query1 = " select ngaygio from nhatkyhethong ";
                rss = stmt.executeQuery(query1);
                while (rss.next()) {
                    String x = rss.getString(1);
                    String ngaygio[] = x.split(" ");
                    String y = ngaygio[0];
                    String z = ngaygio[1];
                    String hoursinfo[] = z.split(":");
                    int gio = Integer.parseInt(hoursinfo[0]);
                    int phut = Integer.parseInt(hoursinfo[1]);
                    Date date3 = fm.parse(y);
                    c3.setTime(date3);
                    if (c1.before(c2)) {
                        if (c1.before(c3) && c2.after(c3)) {
                            rsdate = rsdate + x + ";";
                        }
                        if (c3.equals(c1)) {
                            if (gio > giotu) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                        if (c3.equals(c2)) {
                            if (gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == gioden && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                    if (c1.equals(c2)) {
                        if (c3.equals(c1)) {
                            if (gio > giotu && gio < gioden) {
                                rsdate = rsdate + x + ";";
                            }
                            if (gio == giotu && phut >= phuttu && phut <= phutden) {
                                rsdate = rsdate + x + ";";
                            }
                        }
                    }
                }
            }
            String query = " select MaNK, NgayGio, HoTen, TenLTT, TenTS "
                    + " from nhatkyhethong A, nhanvien B, loaithaotac C, thamsohethong D "
                    + " where A.MaNV = B.MaNV and A.MaLTT = C.MaLTT and A.MaTS = D.MaTS "
                    + "    and C.MaNN = " + langid + " and D.MaNN = " + langid + " ";
            String[] arr = {nhanvien, thaotac, doituong};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && arr[i].equals(doituong)) {
                    query = query + " and TenTS like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(thaotac)) {
                    query = query + " and TenLTT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && arr[i].equals(nhanvien)) {
                    query = query + " and HoTen like '%" + arr[i] + "%' ";
                }
            }
            if (n == 0) {
                String dateqry[] = rsdate.split(";");
                String x = "";
                for (int i = 0; i < dateqry.length; i++) {
                    x = x + " '" + dateqry[i] + "' ,";
                }
                String datequery = x.substring(0, x.length() - 1);
                query = query + " and NgayGio in (" + datequery + ") ";
            }
            query = query + " Order by TenTS  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhatKy nk = new Beans.NhatKy();
                nk.setMaNK(rss.getInt(1));
                nk.setNgayGio(rss.getString(2));
                nk.setTenNV(rss.getString(3));
                nk.setTenLTT(rss.getString(4));
                nk.setTenTS(rss.getString(5));
                v.add(nk);
            }
            stmt.close();
            rss.close();

            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean DeleteKV(String mank) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Delete from nhatkyhethong where MaNK = " + mank;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckDate(String tungay, String tugio, String tuphut, String denngay, String dengio, String denphut) {
        try {
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            int n = 0;
            if (!tungay.equals("") && !tugio.equals("")) {
                int giotu = Integer.parseInt(tugio);
                int phuttu = Integer.parseInt(tuphut);
                int gioden = Integer.parseInt(dengio);
                int phutden = Integer.parseInt(denphut);
                if (giotu >= 25 || gioden >= 25 || phuttu >= 61 || phutden >= 61) {
                    n = n + 1;
                }
                if (tungay.equals(denngay)) {
                    if (giotu > gioden) {
                        n = n + 1;
                    }
                    if (giotu == gioden) {
                        if (phuttu >= phutden) {
                            n = n + 1;
                        }
                    }
                } else {
                    Calendar c1 = Calendar.getInstance();
                    Calendar c2 = Calendar.getInstance();
                    Date date1 = fm.parse(tungay);
                    Date date2 = fm.parse(denngay);
                    c1.setTime(date1);
                    c2.setTime(date2);
                    if (c1.after(c2)) {
                        n = n + 1;
                    }
                }
            }
            if (n == 0) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        }
    }
}
